## Don't take a dependency on the whole SQL 2008 cmdlet/provider when it's so lame
## Instead we adapt core pieces from SQLPSX - www.codeplex.com/SQLPSX - and update 
## them for Powershell v2.0

try 
{
    [system.reflection.assembly]::Load("Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | write-verbose
    [system.reflection.assembly]::Load("Microsoft.SqlServer.SmoExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | write-verbose
}
catch { write-verbose $_.ToString() }

function Get-SqlConnection
{
    [CmdletBinding()]
    param (
        [parameter(Mandatory=$True, Position=0, ValueFromPipeline=$true)]
        [string]$ServerName,
        [parameter(Position=1)]
        [string]$Username,
        [parameter(Position=2)]
        [string]$Password,
        [parameter(Position=3)]
        [int]$Timeout=30
    )
    
    if($Username -and $Password)
        { $con = new-object Microsoft.SqlServer.Management.Common.ServerConnection $ServerName,$Username,$Password }
    else
        { $con = new-object Microsoft.SqlServer.Management.Common.ServerConnection $ServerName }
	
    $con.StatementTimeout = $Timeout
    $con.Connect()

    return $con    
}

function Get-SqlServer
{
    [CmdletBinding()]
    param (
        [parameter(Mandatory=$True, Position=0, ValueFromPipeline=$true)]
        [string]$ServerName,
        [parameter(Position=1)]
        [string]$Username,
        [parameter(Position=2)]
        [string]$Password,
        [parameter(Position=3)]
        [int]$Timeout
    )
    
    # When $sqlserver passed in from the SMO Name property, brackets
    # are automatically inserted which then need to be removed
    $ServerName = $ServerName -replace "\[|\]"

    $con = Get-SqlConnection $ServerName $Username $Password $Timeout

    $Server = new-object Microsoft.SqlServer.Management.Smo.Server $con
    $Server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Database], "IsSystemObject")
    $Server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject")
    $Server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject")
    $Server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
    $Server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject")
    
    return $Server   
}

function Get-SqlDatabase
{ 
    [CmdletBinding()]
    param (
        [parameter(Mandatory=$True, Position=0, ValueFromPipeline=$true)]
        [Microsoft.SqlServer.Management.Smo.Server]$Server,
        [parameter(Position=1)]
        [string]$Name = "*",
        [parameter()]
        [switch]$Force
    )
        
    $Server.Databases | 
        where { 
            $_.Name -like $Name                              `
            -and (!$_.IsSystemObject -or $Force)   `
            -and $_.IsAccessible 
        }
}

function Invoke-SqlBackup
{
    [CmdletBinding()]
    param (
        [parameter(Mandatory=$True, Position=0)]
        [Microsoft.SqlServer.Management.Smo.Server]$Server,
        [parameter(Mandatory=$true, position=1)]
        [string]$DatabaseName,
        [parameter(Mandatory=$true, position=2)]
        [string[]]$Path,
        [Parameter()]
        [string]$Action='Database',   # Values: Database or Log
        [Parameter()]
        [string]$Description='',
        [Parameter()]
        [string]$BackupName='',
        [Parameter()]
        [switch]$Force,
        [Parameter()]
        [switch]$Incremental,
        [Parameter()]
        [switch]$CopyOnly
    )    

    if (!$Server.Databases.Contains("$DatabaseName")) 
        { throw "Database $DatabaseName does not exist on $($Server.Name)." }
        
    $backup = new-object Microsoft.SqlServer.Management.Smo.Backup
    $Path | 
        % {
            Split-Path $_ | Test-Path2 -Throw
            $backupDevice = new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $_, 'File'
            $backup.Devices.Add($backupDevice) 
        }

    $backup.Action = $Action
    $backup.BackupSetDescription = $Description
    $backup.BackupSetName = $BackupName
    $backup.Database = $DatabaseName    
    $backup.Initialize = $Force
    $backup.Incremental = $Incremental
    $backup.CopyOnly = $CopyOnly
    
    # disable timeout
    $temp = $Server.ConnectionContext.StatementTimeout
    $Server.ConnectionContext.StatementTimeout = 0
    
    $backup.SqlBackup($Server) 
    
    $Server.ConnectionContext.StatementTimeout = $temp
    
    trap {
        Write-Error $_
        Write-Exception $_.Exception
        continue
    }
    
    Write-Host "$Action backup of $($Server.Name).$DatabaseName to $Path complete."
} 

function Invoke-SqlRestore
{
    [CmdletBinding()]
    param (
        [parameter(Mandatory=$True, Position=0)]
        [Microsoft.SqlServer.Management.Smo.Server]$Server,
        [parameter(Mandatory=$true, position=1)]
        [string]$DatabaseName,
        [parameter(Mandatory=$true, position=2)]
        [string[]]$Path,
        [Parameter()]
        [string]$Action='Database',   # Values: Database or Log
        [Parameter()]
        [DateTime]$StopAt,
        [Parameter()]
        [string]$DataDir,
        [Parameter()]
        [switch]$Force,
        [Parameter()]
        [switch]$NoRecovery,
        [Parameter()]
        [switch]$KeepReplication        
    )   
        
    $restore = new-object Microsoft.SqlServer.Management.Smo.Restore
    $Path | 
        % {
            Test-Path2 $_ -Throw
            $restoreDevice = new-object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $_, 'File'
            $restore.Devices.Add($restoreDevice) 
        }

    $restore.Action = $Action
    $restore.Database = $DatabaseName
    $restore.ReplaceDatabase = $Force
    $restore.NoRecovery = $NoRecovery
    $restore.KeepReplication = $KeepReplication
   
    if ($StopAt)
        { $restore.ToPointInTime = $stopAt }

    if ($DataDir)
    {
       foreach ($file in $restore.ReadFileList($Server))
        {            
            $logicalName = $file.logicalName
                
            switch ($file.type)
            {
                "D" { $physicalName = join-path $DataDir "$DatabaseName.mdf" }
                "L" { $physicalName = join-path $DataDir "$DatabaseName.ldf" }
                "F" { $physicalName = join-path $DataDir $logicalName.Split("_")[1]
                      mkdir $physicalName -ea SilentlyContinue 
                    }
            }
            
            $relocateFile = new-object Microsoft.SqlServer.Management.Smo.RelocateFile $logicalName, $physicalName
            $restore.RelocateFiles.Add($relocateFile) | out-null
        }
    }
    
    # disable timeout
    $temp = $Server.ConnectionContext.StatementTimeout
    $Server.ConnectionContext.StatementTimeout = 0
    
    $restore.SqlRestore($Server) 
    
    $Server.ConnectionContext.StatementTimeout = $temp
    
    trap {
        Write-Error $_
        Write-Exception $_.Exception
        continue
    }
    
    Write-Host "$Action restore of $($Server.Name).$DatabaseName from $(@($Path)[0]) complete."
}